/**
 * ExcelAnalyze.java
 * Created at 2015年11月19日
 * Created by Jackie Liu
 * Copyright (C) 2015 TONGHE, All rights reserved.
 */
package com.huabo.excel;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import org.apache.commons.io.FilenameUtils;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.IOException;
import java.text.DecimalFormat;

/**
 * <p>ClassName: ExcelAnalyze</p>
 * <p>Description: excel解析</p>
 * <p>Author: Jackie Liu</p>
 * <p>Date: 2015年11月19日</p>
 */
public class ExcelAnalyze {

	private boolean is_xls;
	private int total_row;
	private int total_sheet;
	
	private FileInputStream fis;
	
	//2003解析对象
	private Workbook workBook;
	private Sheet current_jxl_sheet;
	private Cell[] current_jxl_row;
	
	//2007解析对象
	private XSSFWorkbook xssfWorkbook;
	private XSSFSheet current_poi_sheet;
	private XSSFRow current_poi_row;
	
	/**
	 * <p>Description: 无参构造函数</p>
	 */
	public ExcelAnalyze() {}

	/**
	 * <p>Description: 在构造函数中装载Excel文档流</p>
	 * @param filePath Excel文档路径
	 * @throws BiffException
	 * @throws RuntimeException
	 * @throws IOException
	 */
	public ExcelAnalyze(String filePath) throws BiffException, RuntimeException, IOException {
		readExcel(filePath);
	}
	
	/**
	 * <p>Description: 装载Excel文档流</p>
	 * @param filePath Excel文档路径
	 * @throws RuntimeException
	 * @throws BiffException
	 * @throws IOException
	 */
	public void readExcel(String filePath) throws RuntimeException, BiffException, IOException {
		//String[] extNames = filePath.split("\\.");
		//String extName = extNames[extNames.length - 1];
	    String extName = FilenameUtils.getExtension(filePath).toLowerCase();
		if ("xls".equals(extName)) {
			is_xls = true;
			read2003(filePath);
		} else if ("xlsx".equals(extName)) {
			is_xls = false;
			read2007(filePath);
		} else {
			throw new RuntimeException("文件扩展名非xls、xlsx");
		}
	}
	
	private void read2003(String filePath) throws BiffException, IOException {
		fis = new FileInputStream(filePath);   
		workBook = Workbook.getWorkbook(fis);  
		Sheet[] sheets = workBook.getSheets();
		total_sheet = sheets.length;
        current_jxl_sheet = sheets[0];
        total_row = current_jxl_sheet.getRows();  
	}
	
	private void read2007(String filePath) throws IOException {
		fis = new FileInputStream(filePath);
        xssfWorkbook = new XSSFWorkbook(fis);
        total_sheet = xssfWorkbook.getNumberOfSheets();
        current_poi_sheet = xssfWorkbook.getSheetAt(0);
        total_row = current_poi_sheet.getLastRowNum() + 1;
	}
	
	/**
	 * <p>Description: 关闭Excel输入流</p>
	 * @throws IOException
	 */
    public void close() throws IOException {
    	if (fis != null) {
    		fis.close();
    	}
    	if (workBook != null) {
    		workBook.close();
    	}
    	/*if (xssfWorkbook != null) {
    		xssfWorkbook.close();
    	}*/
    }
        
    /**
     * <p>Description: 获取当前sheet对象的总行数</p>
     * @return
     */
	public int getTotalRow() {
		return total_row;
	}
	
	/**
	 * <p>Description: 获取Excel文件的总sheet数</p>
	 * @return
	 */
	public int getTotalSheet() {
		return total_sheet;
	}
	
	/**
	 * <p>Description: 设置当前sheet对象</p>
	 * @param sheetNum sheet索引
	 * @return 当前sheet对象是否为null
	 */
	public boolean setCurrentSheet(int sheetNum) {
		if (sheetNum >= total_sheet) {
			throw new RuntimeException("超出Excel文件sheet总数");
		}
		
		total_row = 0;
		if (is_xls) {
			current_jxl_sheet = workBook.getSheets()[sheetNum];
			if (current_jxl_sheet == null) {
				return true;
			}
	        total_row = current_jxl_sheet.getRows();  
		} else {
			current_poi_sheet = xssfWorkbook.getSheetAt(sheetNum);
			if (current_poi_sheet == null) {
				return true;
			}
			total_row = current_poi_sheet.getLastRowNum() + 1;
		}
		
		return false;
	}
	
	/**
	 * <p>Description: 设置当前Row对象</p>
	 * @param rowNum Row索引
	 * @return 当前Row对象是否为null
	 */
	public boolean setCurrentRow(int rowNum) {
		if (rowNum >= total_row) {
			throw new RuntimeException("超出Excel文件当前sheet总行数");
		}
		
		if (is_xls) {
			current_jxl_row = current_jxl_sheet.getRow(rowNum);
			if (current_jxl_row == null) {
				return true;
			}
		} else {
			current_poi_row = current_poi_sheet.getRow(rowNum);
			if (current_poi_row == null) {
				return true;
			}
		}
		return false;
	}
	
	@SuppressWarnings("static-access")
	private String getXSSFCellValue(XSSFCell xssfRow) {
	    if (xssfRow == null) {
	        return "";
	    }
        if (xssfRow.getCellType() == XSSFCell.CELL_TYPE_BOOLEAN) {
            return String.valueOf(xssfRow.getBooleanCellValue());
        } else if (xssfRow.getCellType() == XSSFCell.CELL_TYPE_NUMERIC) {
        	DecimalFormat format = new DecimalFormat("0");
        	String value = format.format(xssfRow.getNumericCellValue());
            return value;
        } else {
            return String.valueOf(xssfRow.getStringCellValue());
        }
    }
	
	private String getValue(int columNum) {
		String value = null;
		if (is_xls) {
			if (current_jxl_row != null && columNum < current_jxl_row.length) {
				value = current_jxl_row[columNum].getContents();
			}
		} else {
			if (current_poi_row != null && columNum <= current_poi_row.getLastCellNum()) {
				value = getXSSFCellValue(current_poi_row.getCell(columNum));
			}
		}
		if (value == null || "".equals(value)) {
			return null;
		}
		return value == null ? null : value.trim();
	}
	
	public String getString(int columNum) {
		return getValue(columNum);
	}
	
	public String getString(int columNum, String defalutValue) {
		String value = getValue(columNum);
		return value == null ? defalutValue : value;
	}
	
	public double getDouble(int columNum) {
		String value = getValue(columNum);
		return value == null ? 0 : Double.parseDouble(value);
	}
	
	public Double getDouble(int columNum, double defalutValue) {
		String value = getValue(columNum);
		return value == null ? defalutValue : Double.valueOf(value);
	}
	
	public long getLong(int columNum) {
		String value = getValue(columNum);
		return value == null ? 0 : Long.parseLong(value);
	}
	
	public Long getLong(int columNum, long defalutValue) {
		String value = getValue(columNum);
		return value == null ? defalutValue : Long.parseLong(value);
	}
	
	public float getFloat(int columNum) {
		String value = getValue(columNum);
		return value == null ? 0 : Float.parseFloat(value);
	}
	
	public Float getFloat(int columNum, float defalutValue) {
		String value = getValue(columNum);
		return value == null ? defalutValue : Float.valueOf(value);
	}
	
	public boolean getBoolean(int columNum) {
		String value = getValue(columNum);
		return value == null ? false : Boolean.parseBoolean(value);
	}
	
	public Boolean getBoolean(int columNum, boolean defalutValue) {
		String value = getValue(columNum);
		return value == null ? defalutValue : Boolean.valueOf(value);
	}
	
	public int getInteger(int columNum) {
		String value = getValue(columNum);
		return value == null ? 0 : Integer.parseInt(value);
	}
	
	public Integer getInteger(int columNum, int defalutValue) {
		String value = getValue(columNum);
		return value == null ? defalutValue : Integer.parseInt(value);
	}
	
	/*public static void main(String[] args) {
		try {
			long startTime = System.currentTimeMillis();
			
			ExcelAnalyze ea = new ExcelAnalyze("D:/download/test_a.xls");
			System.out.println("total_sheet = " + ea.getTotalSheet());
			System.out.println("total_row = " + ea.getTotalRow());
			
			int rows = ea.getTotalRow();
			for (int i = 0; i < rows; i ++) {
				ea.setCurrentRow(i);
				System.out.println(i + " : " + ea.getString(0));
			}
			ea.close();
			
			long endTime = System.currentTimeMillis();
			
			double sec = DoubleUtils.div(endTime - startTime, 1000);
			System.out.println("sec = " + sec);
			System.out.println("sec = " + (endTime - startTime));
		} catch (BiffException e) {
			e.printStackTrace();
		} catch (RuntimeException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}*/
}
